package com.vegas.netspider.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.vegas.netspider.dto.SportNote;

public class NoteDbHandler extends DbHandler{

	private String insertSql="insert into sport_note(content,displayed,createTime, creater) values(?,?,?,?)";
	public void save(final SportNote sn){
		jdbcTemplate.update(insertSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setString(1, sn.getContent());
				p.setBoolean(2, sn.isDisplayed());
				p.setLong(3, System.currentTimeMillis());
				p.setString(4, sn.getCreater());
			}
		});
	}
	
	private String listSql="select id,content,createTime,creater from sport_note where displayed=? limit ?,?";
	public List<SportNote> list(final int index,final int size){
		return jdbcTemplate.query(listSql, new PreparedStatementSetter(){

			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setBoolean(1, true);
				p.setInt(2, index);
				p.setInt(3, size+index);
			}
			
		},new RowMapper<SportNote>() {

			@Override
			public SportNote mapRow(ResultSet r, int arg1)
					throws SQLException {
				SportNote sn = new SportNote();
				sn.setId(r.getInt("id"));
				sn.setContent(r.getString("content"));
				sn.setCreateTime(r.getLong("createTime"));
				sn.setCreater(r.getString("creater"));
				return sn;
			}
			
		});
	}

}
